UCF STIG Viewer Logo
Changes are coming to https://stigviewer.com. Take our survey to help us understand your usage and how we can better serve you in the future.
Take Survey

SQL Server must ensure that remote sessions that access an organization-defined list of security functions and security-relevant information are audited.


Overview

Finding ID Version Rule ID IA Controls Severity
V-41307 SQL2-00-001600 SV-53789r1_rule Medium
Description
Remote access is any access to an organizational information system by a user (or an information system) communicating through an external, non-organization-controlled network (e.g., the Internet). Examples of remote access methods include dial-up, broadband, and wireless. Remote network and system access is accomplished by leveraging common communication protocols to establish a remote connection. These connections will typically originate over either the public Internet or the Public Switched Telephone Network (PSTN). Neither of these internetworking mechanisms is private or secure, and they do not by default restrict access to networked resources once connectivity is established. Numerous best practices are employed to protect remote connections, such as utilizing encryption to protect data sessions and firewalls to restrict and control network connectivity. In addition to these protections, auditing must also be utilized in order to track system activity, assist in diagnosing system issues, and provide evidence needed for forensic investigations post security incident.
STIG Date
Microsoft SQL Server 2012 Database Instance Security Technical Implementation Guide 2014-01-05

Details

Check Text ( C-47876r2_chk )
Check to see that all required events are being audited.
From the query prompt:

SELECT DISTINCT traceid FROM ::FN_TRACE_GETINFO('0')

All currently defined traces for the SQL server instance will be listed. If no traces are returned, this is a finding.

Determine the trace being used for the auditing requirement. Replace # with a traceid being used for the auditing requirements.

From the query prompt:
SELECT DISTINCT(eventid) FROM ::FN_TRACE_GETEVENTINFO('#')
The required eventids 14, 15, 18, 20, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 115, 116, 117, 118, 128, 129, 130, 131, 132, 133, 134, 135, 152, 153, 170, 171, 172, 173, 175, 176, 177 and 178 should be listed. If any of the audit events or eventids required above are not listed, this is finding.
Fix Text (F-46698r2_fix)
Create and start an audit trace that audits required events.
CREATE PROCEDURE fso_audit AS
-- Create a Queue
DECLARE @rc INT
DECLARE @TraceID INT
DECLARE @maxfilesize BIGINT
DECLARE @fso_audit_log NVARCHAR(128)
SET @maxfilesize = 5
-- Define custom @fso_audit_log to path\filename
SET @fso_audit_log = 'd:\sqlserver\audit\fsoauditlog.log'
EXEC @rc = SP_TRACE_CREATE @TraceID output, 6, @fso_audit_log, @maxfilesize, NULL
IF (@rc != 0) GOTO Error
-- Client side File and Table cannot be scripted.
-- Set the events:
DECLARE @on BIT
SET @on = 1
-- Logins are audited based on SQL Server instance
-- setting Audit Level stored in registry
-- HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.[#]
\MSSQLServer\AuditLevel
-- Audit Login
-- Occurs when a user successfully logs in to SQL Server.
EXEC SP_TRACE_SETEVENT @TraceID, 14, 1, @on -- TextData
EXEC SP_TRACE_SETEVENT @TraceID, 14, 6, @on -- NTUserName
EXEC SP_TRACE_SETEVENT @TraceID, 14, 7, @on -- NTDomainName
EXEC SP_TRACE_SETEVENT @TraceID, 14, 8, @on -- HostName
EXEC SP_TRACE_SETEVENT @TraceID, 14, 10, @on -- ApplicationName
EXEC SP_TRACE_SETEVENT @TraceID, 14, 11, @on -- LoginName
EXEC SP_TRACE_SETEVENT @TraceID, 14, 12, @on -- SPID
EXEC SP_TRACE_SETEVENT @TraceID, 14, 14, @on -- StartTime
EXEC SP_TRACE_SETEVENT @TraceID, 14, 23, @on -- Success
EXEC SP_TRACE_SETEVENT @TraceID, 14, 26, @on -- ServerName
EXEC SP_TRACE_SETEVENT @TraceID, 14, 35, @on -- DatabaseName
EXEC SP_TRACE_SETEVENT @TraceID, 14, 41, @on -- LoginSid
EXEC SP_TRACE_SETEVENT @TraceID, 14, 60, @on -- IsSystem
EXEC SP_TRACE_SETEVENT @TraceID, 14, 64, @on -- SessionLoginName
-- Audit Logout
-- Occurs when a user logs out of SQL Server.
EXEC SP_TRACE_SETEVENT @TraceID, 15, 6, @on -- NTUserName
EXEC SP_TRACE_SETEVENT @TraceID, 15, 7, @on -- NTDomainName
EXEC SP_TRACE_SETEVENT @TraceID, 15, 8, @on -- HostName
EXEC SP_TRACE_SETEVENT @TraceID, 15, 10, @on -- ApplicationName
EXEC SP_TRACE_SETEVENT @TraceID, 15, 11, @on -- LoginName
EXEC SP_TRACE_SETEVENT @TraceID, 15, 12, @on -- SPID
EXEC SP_TRACE_SETEVENT @TraceID, 15, 13, @on -- Duration
EXEC SP_TRACE_SETEVENT @TraceID, 15, 14, @on -- StartTime
EXEC SP_TRACE_SETEVENT @TraceID, 15, 15, @on -- EndTime
EXEC SP_TRACE_SETEVENT @TraceID, 15, 23, @on -- Success
EXEC SP_TRACE_SETEVENT @TraceID, 15, 26, @on -- ServerName
EXEC SP_TRACE_SETEVENT @TraceID, 15, 35, @on -- DatabaseName
EXEC SP_TRACE_SETEVENT @TraceID, 15, 41, @on -- LoginSid
EXEC SP_TRACE_SETEVENT @TraceID, 15, 60, @on -- IsSystem
EXEC SP_TRACE_SETEVENT @TraceID, 15, 64, @on -- SessionLoginName
-- Audit Server Starts and Stops
-- Occurs when the SQL Server service state is modified.
EXEC SP_TRACE_SETEVENT @TraceID, 18, 6, @on -- NTUserName
EXEC SP_TRACE_SETEVENT @TraceID, 18, 7, @on -- NTDomainName
EXEC SP_TRACE_SETEVENT @TraceID, 18, 8, @on -- HostName
EXEC SP_TRACE_SETEVENT @TraceID, 18, 10, @on -- ApplicationName
EXEC SP_TRACE_SETEVENT @TraceID, 18, 11, @on -- LoginName
EXEC SP_TRACE_SETEVENT @TraceID, 18, 12, @on -- SPID
EXEC SP_TRACE_SETEVENT @TraceID, 18, 14, @on -- StartTime
EXEC SP_TRACE_SETEVENT @TraceID, 18, 23, @on -- Success
EXEC SP_TRACE_SETEVENT @TraceID, 18, 26, @on -- ServerName
EXEC SP_TRACE_SETEVENT @TraceID, 18, 41, @on -- LoginSid
EXEC SP_TRACE_SETEVENT @TraceID, 18, 60, @on -- IsSystem
EXEC SP_TRACE_SETEVENT @TraceID, 18, 64, @on -- SessionLoginName
-- Audit Login Failed
-- Indicates that a login attempt to SQL Server from a client failed.
EXEC SP_TRACE_SETEVENT @TraceID, 20, 1, @on -- TextData
EXEC SP_TRACE_SETEVENT @TraceID, 20, 6, @on -- NTUserName
EXEC SP_TRACE_SETEVENT @TraceID, 20, 7, @on -- NTDomainName
EXEC SP_TRACE_SETEVENT @TraceID, 20, 8, @on -- HostName
EXEC SP_TRACE_SETEVENT @TraceID, 20, 10, @on -- ApplicationName
EXEC SP_TRACE_SETEVENT @TraceID, 20, 11, @on -- LoginName
EXEC SP_TRACE_SETEVENT @TraceID, 20, 12, @on -- SPID
EXEC SP_TRACE_SETEVENT @TraceID, 20, 14, @on -- StartTime
EXEC SP_TRACE_SET